/*
 * @(#)GsMngDao.java
 * 2011-8-21 下午10:05:50
 *
 * Copyright (c) 2018-2028, HangZhou QiYun InfoTech Co.,Ltd. .
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.qyxx.platform.gsmng.common.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.jdbc.Work;
import org.hibernate.transform.Transformers;
import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.stereotype.Repository;

import com.qyxx.platform.common.orm.hibernate.DynamicHibernateDao;
import com.qyxx.platform.gsc.cache.SqlCache;


/**
 *  业务管理通用DAO
 *  @author gxj
 *  @version 1.0
 *  @since 1.6 2011-8-21 下午10:05:50
 */
@Repository
@Scope("prototype")
public class GsMngDao extends DynamicHibernateDao<Map<String, Object>, Long> {

	/**
	 * 默认构造函数
	 */
	public GsMngDao() {
		super();
	}
	
	/**
	 * @param entityName
	 */
	public GsMngDao(String entityName) {
		super(entityName);
	}

	/**
	 * @param sessionFactory
	 * @param entityName
	 */
	public GsMngDao(SessionFactory sessionFactory, String entityName) {
		super(sessionFactory, entityName);
	}
	
	/**
	 * 查询同类型最大编号值
	 * 
	 * @param entityName
	 * @param key
	 * @param prefixVal
	 * @return
	 */
	public String getMaxAutoIdNo(String entityName, String key, String prefixVal) {
		String hql = "select " + key + " from " + entityName + " where " + key + " like ? order by " + key + " desc";
		Query ql = this.createQuery(hql, prefixVal+"%");
		ql.setFirstResult(0);
		ql.setMaxResults(1);
		Object obj = ql.uniqueResult();
		return (String)obj;
	}
	
	/**
	 * 插入表记录并返回主键值
	 * 
	 * @param sql
	 * @param paramMap
	 * @return
	 */
	public List<Long> insertDataWithReturnKeys(final String sql, final Map<String, Object> paramMap) {
		final List<Long> ids = new ArrayList<Long>();
		Work work = new Work() {
			public void execute(Connection connection)
														throws SQLException {
				// 通过JDBC API执行用于批量更新的SQL语句
				PreparedStatement stmt = null;
				ResultSet rs = null;
				try {
					String sqlname = NamedParameterUtils.parseSqlStatementIntoString(sql);
					Object[] args = NamedParameterUtils.buildValueArray(sql, paramMap);
					stmt = connection.prepareStatement(sqlname, Statement.RETURN_GENERATED_KEYS);
					ArgPreparedStatementSetter aps = new ArgPreparedStatementSetter(args);
					aps.setValues(stmt);
					int count = stmt.executeUpdate();
					if(count > 0) {
						rs = stmt.getGeneratedKeys();
						if(null!=rs) {
							if(rs.next()) {
								Long id = (long) rs.getInt(1);
								for(int i=1;i<count;i++) {
									Long tid = id - (count - i);
									ids.add(tid);
								}
								ids.add(id);
							}
						}
					}
				} finally {
					if (rs != null) {
						try {
							rs.close();
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
					if (stmt != null) {
						try {
							stmt.close();
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				}
			}
		};
		getSession().doWork(work);
		return ids;
	}

	/**
	 * 创建存储过程
	 * 
	 * @param procContent
	 * @return
	 */
	public void createProcedure(final String procedureContent) {
		Work work = new Work() {
			public void execute(Connection conn) throws SQLException {
				PreparedStatement pstmt = null;
				try {
					pstmt = conn.prepareStatement(procedureContent);
					pstmt.executeUpdate();
				} finally {
					try {
						if(null!=pstmt) {
							pstmt.close();
							pstmt = null;
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}

				}
			}
		};
		getSession().doWork(work);
	}
	
	/**
	 * 调用存储过程并返回值
	 * 
	 * @param procedureKey
	 * @param paramMap
	 * @return
	 */
	public String callProcedureWithResult(final String procedureKey,final Map<String, Object> paramMap) {
		final int num = paramMap.size()+1;
		StringBuffer temp = new StringBuffer();
		for (int i = 0; i < num; i++) {
			temp.append("?");
			if(i<num-1){
				temp.append(",");
			}
		}
		final String paramNum = temp.toString();
		final StringBuffer result = new StringBuffer(); 
		Work work = new Work() {
			public void execute(Connection conn) throws SQLException {
				CallableStatement cstmt = null;
				 try {  
					    cstmt = conn.prepareCall("{call "+procedureKey+"("+paramNum+")}");  
				        for(Map.Entry<String, Object> en : paramMap.entrySet()) {
							String key =en.getKey();
							Object val =en.getValue();
							if(null!=val) {
								if(val instanceof String ){
									String value = String.valueOf(val);
									cstmt.setString(key, value);
								}else if(val instanceof Long ){
									Long value = (Long)val;
									cstmt.setLong(key, value);
								}else if(val instanceof Integer ){
									int value = (Integer)val;
									cstmt.setInt(key, value);
								}else if(val instanceof Double ){
									double value = (Double)val;
									cstmt.setDouble(key, value);
								}else if(val instanceof Date) {
									Date value = (Date)val;
									cstmt.setTimestamp(key, new Timestamp(value.getTime()));
								}
							} else {
								if(val instanceof String ){
									cstmt.setNull(key, Types.VARCHAR);
								}else if(val instanceof Long ){
									cstmt.setNull(key, Types.BIGINT);
								}else if(val instanceof Integer ){
									cstmt.setNull(key, Types.INTEGER);
								}else if(val instanceof Double ){
									cstmt.setNull(key, Types.DOUBLE);
								}else if(val instanceof Date) {
									cstmt.setNull(key, Types.TIMESTAMP);
								}
							}
						}
				        cstmt.registerOutParameter(num, java.sql.Types.VARCHAR);
				        cstmt.execute();
				        result.append(cstmt.getString(num));
				    } finally{  
				        try {  
				            if(null!=cstmt) {
				            	cstmt.close();  
					            cstmt = null;
				            }
				        } catch (SQLException e) {  
				            e.printStackTrace();  
				        }  
				    }  
			}
		};
		getSession().doWork(work);
		return result.toString();
	}
	
	/**
	 * 调用存储过程
	 * 
	 * @param procedureKey
	 * @param paramMap
	 * @return
	 */
	public void callProcedure(final String procedureKey,final Map<String, Object> paramMap) {
		
		Long num = (long)paramMap.size();
		StringBuffer temp = new StringBuffer();
		for (int i = 0; i < num; i++) {
			temp.append("?");
			if(i<num-1){
				temp.append(",");
			}
		}
		
		final String paramNum = temp.toString();
		
		Work work = new Work() {
			public void execute(Connection conn) throws SQLException {
				CallableStatement cstmt = null;
				 try {  
				        cstmt = conn.prepareCall("{call "+procedureKey+"("+paramNum+")}");  
				        for(Map.Entry<String, Object> en : paramMap.entrySet()) {
							String key =en.getKey();
							Object val =en.getValue();
							if(null!=val) {
								if(val instanceof String ){
									String value = String.valueOf(val);
									cstmt.setString(key, value);
								}else if(val instanceof Long ){
									Long value = (Long)val;
									cstmt.setLong(key, value);
								}else if(val instanceof Integer ){
									int value = (Integer)val;
									cstmt.setInt(key, value);
								}else if(val instanceof Double ){
									double value = (Double)val;
									cstmt.setDouble(key, value);
								}else if(val instanceof Date) {
									Date value = (Date)val;
									cstmt.setTimestamp(key, new Timestamp(value.getTime()));
								}
							} else {
								if(val instanceof String ){
									cstmt.setNull(key, Types.VARCHAR);
								}else if(val instanceof Long ){
									cstmt.setNull(key, Types.BIGINT);
								}else if(val instanceof Integer ){
									cstmt.setNull(key, Types.INTEGER);
								}else if(val instanceof Double ){
									cstmt.setNull(key, Types.DOUBLE);
								}else if(val instanceof Date) {
									cstmt.setNull(key, Types.TIMESTAMP);
								}
							}
						}
				        cstmt.execute();
				    } finally{  
				        try {  
				            if(null!=cstmt) {
				            	cstmt.close();  
					            cstmt = null;
				            }
				        } catch (SQLException e) {  
				            e.printStackTrace();  
				        }  
				    }  
			}
		};
		getSession().doWork(work);
	}
	
	/**
	 * 根据实体名和实体编号获取关联实体对象
	 * 
	 * @param referEntityName
	 * @param entityName
	 * @param entityId
	 * @return
	 */
	public Map<String, Object> getReferEntity(String referEntityName, String entityName, Long entityId) {
		String sqlKey = referEntityName + com.qyxx.platform.gsc.utils.Constants.NAME_SPLIT_SYMBOL
				+ com.qyxx.platform.gsc.utils.Constants.COMMON_QUERY_SQL_KEY;
		String hqlSelect = SqlCache.getInstance().getSqlContent(sqlKey);
		String hql = hqlSelect + " and a.entityName = ? and a.entityId = ?";
		return findUniqueByHql(hql, entityName, entityId);
	}
	
	/**
	 * 根据实体名、实体编号和库存系数获取关联实体对象
	 * 
	 * @param referEntityName
	 * @param entityName
	 * @param entityId
	 * @param stockRatio
	 * @return
	 */
	public Map<String, Object> getReferEntity(String referEntityName, String entityName, Long entityId, Long stockRatio) {
		String sqlKey = referEntityName + com.qyxx.platform.gsc.utils.Constants.NAME_SPLIT_SYMBOL
				+ com.qyxx.platform.gsc.utils.Constants.COMMON_QUERY_SQL_KEY;
		String hqlSelect = SqlCache.getInstance().getSqlContent(sqlKey);
		String hql = hqlSelect + " and a.entityName = ? and a.entityId = ? and a.stockRatio = ?";
		return findUniqueByHql(hql, entityName, entityId, stockRatio);
	}
	
	/**
	 * 删除关联实体对象
	 * 
	 * @param referEntityName
	 * @param entityName
	 * @param entityId
	 */
	public void deleteReferEntity(String referEntityName, String entityName, Long entityId) {
		String hql = "delete " + referEntityName + " where entityName = ? and entityId = ?";
		batchExecute(hql, entityName, entityId);
	}
	
	/**
	 * 根据主表ID删除子表关联实体对象
	 * 
	 * @param referEntityName
	 * @param entityName
	 * @param entityId
	 */
	public void deleteReferEntity(String referEntityName, String entityName, String refMainEntityName, Long mainEntityId) {
		String hql = "delete " + referEntityName + " where entityName = ? and entityId in (select id from " 
					+ entityName + " where " + refMainEntityName +".id = ? )";
		batchExecute(hql, entityName, mainEntityId);
	}
	
	/**
	 * 更新子表字段
	 * 
	 * @param entityName
	 * @param refMainEntityName
	 * @param fieldName
	 * @param val
	 * @param mainEntityId
	 */
	public void updateSubEntity(String entityName, String refMainEntityName, Long mainEntityId, String fieldName, Object val) {
		String hql = "update " + entityName + " set " + fieldName + " = ? where " + refMainEntityName +".id = ?";
		batchExecute(hql, val, mainEntityId);
	}
	
	/**
	 * 更新关联表字段
	 * 
	 * @param referEntityName
	 * @param entityName
	 * @param refMainEntityName
	 * @param fieldName
	 * @param val
	 * @param mainEntityId
	 */
	public void updateReferEntity(String referEntityName, String entityName, 
			String refMainEntityName, String fieldName, Object val, Long mainEntityId) {
		String hql = "update " + referEntityName + " set " + fieldName + " = ? where entityName = ? and entityId in (select id from " 
				+ entityName + " where " + refMainEntityName +".id = ? )";
		batchExecute(hql, val, entityName, mainEntityId);
	}
	/**
	 * 更新表多条记录字段
	 * 
	 * @param entityName
	 * @param fieldName
	 * @param createUserId && entityIds
	 * @param paramNum
	 */
	public void updateEntityFields(String entityName,List<Long> entityIds, String fieldName,String paramNum) {
		String hql = "update " + entityName + " set " + fieldName + " = ? where id in("+paramNum+")";
		batchExecute(hql,entityIds.toArray());
	}
	
	/**
	 * 根据实体名和参数获取实体数量，HQL方式
	 * 
	 * @param entityName
	 * @param param
	 * @return
	 */
	public Long getEntityCountByHql(String entityName, Map<String, Object> param) {
		String hql = " from " + entityName + " where 1=1";
		List<Object> objList = new ArrayList<Object>();
		for(Map.Entry<String, Object> en : param.entrySet()) {
			hql += " and " + en.getKey() + " = ?";
			objList.add(en.getValue());
		}
		return getTotalCountByHql(hql, objList.toArray());
	}
	
	/**
	 * 根据实体名和参数获取实体数量，SQL方式
	 * 
	 * @param entityName
	 * @param param
	 * @return
	 */
	public Long getEntityCountBySql(String entityName, Map<String, Object> param) {
		String sqlKey = entityName + com.qyxx.platform.gsc.utils.Constants.NAME_SPLIT_SYMBOL
				+ com.qyxx.platform.gsc.utils.Constants.QUERY_LIST_SQL_KEY;
		String hql = SqlCache.getInstance().getSqlContent(sqlKey);
		List<Object> objList = new ArrayList<Object>();
		for(Map.Entry<String, Object> en : param.entrySet()) {
			hql += " and " + en.getKey() + " = ?";
			objList.add(en.getValue());
		}
		return getTotalCountBySql(hql, objList.toArray());
	}
	
	/**
	 * 根据主表ID删除子表数据
	 * 
	 * @param referEntityName
	 * @param entityName
	 * @param entityId
	 */
	public void deleteSubEntity(String entityName, String refMainEntityName, Long mainEntityId) {
		String hql = "delete " + entityName + " where " + refMainEntityName +".id = ? ";
		batchExecute(hql, mainEntityId);
	}
	
	/**
	 * 通过sql语句更新实体字段
	 * 
	 * @param entityName
	 * @param fieldMap 包含id属性
	 */
	public void updateEntityFields(String entityName, Map<String, Object> fieldMap) {
		StringBuilder sb = new StringBuilder();
		sb.append("update " + entityName + " set ");
		boolean isFirst = true;
		for(String key : fieldMap.keySet()) {
			if(!"id".equalsIgnoreCase(key)) {
				if(!isFirst) {
					sb.append(",");
				}
				sb.append(" " + key + " = :" + key);
				isFirst = false;
			}
		}
		sb.append(" where id = :id ");
		batchExecute(sb.toString(), fieldMap);
	}


}
